// 引入所需模块
const path = require('path');
const fs = require('fs');
const { app } = require('electron');
const sqlite3 = require('sqlite3').verbose();

// 数据库路径：使用 Electron 推荐的用户数据目录
const dbPath = path.join(app.getPath('userData'), 'data.db');

// 初始化数据库
function initDatabase() {
  // 创建新的数据库连接
  const db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
      console.error('数据库连接错误:', err.message);
      return;
    }
    console.log('成功创建并连接到新的SQLite数据库');
  });
  
  return db;
}

// 创建表的通用函数
// mode: 0-先删除后创建 1-直接创建（如果不存在）
function createTable(db, tableName, createSQL, mode = 1) {
  if (mode === 0) {
    // 先删除已存在的表
    const dropSQL = `DROPDROP TABLE IF EXISTS ${tableName}`;
    db.run(dropSQL, (err) => {
      if (err) {
        console.error(`删除${tableName}表失败:`, err.message);
        return;
      }
      console.log(`${tableName}表已删除（如果存在）`);

      // 创建新表
      db.run(createSQL, (err) => {
        if (err) {
          console.error(`创建${tableName}表失败:`, err.message);
        } else {
          console.log(`${tableName}表创建成功`);
        }
      });
    });
  } else {
    // 直接创建表
    db.run(createSQL, (err) => {
      if (err) {
        console.error(`创建${tableName}表失败:`, err.message);
      } else {
        console.log(`${tableName}表创建成功（如果不存在）`);
      }
    });
  }
}

// 创建所有数据表
function createTables(db) {
  // 文章表
  const articleTableSQL = `
    CREATE TABLE IF NOT EXISTS articles (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      article_id TEXT NOT NULL UNIQUE, -- 由时间生成的唯一文章ID
      category TEXT NOT NULL,         -- 文章类别
      create_time DATETIME NOT NULL,  -- 创建日期（精确到秒）
      author TEXT NOT NULL,           -- 作者
      author_avatar_url TEXT,         -- 作者头像URL
      cover_url TEXT,                 -- 封面URL
      title TEXT NOT NULL,            -- 文章标题
      content TEXT,                   -- 文章内容（支持HTML）
      read_time INTEGER,              -- 建议阅读时间（分钟）
      views INTEGER DEFAULT 0,        -- 浏览量，默认0
      UNIQUE(article_id)              -- 确保文章ID唯一
    )
  `;
  createTable(db, 'articles', articleTableSQL);

  // 开发者日志表
  const timelineTableSQL = `
    CREATE TABLE IF NOT EXISTS timeline (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      date TEXT NOT NULL,
      type TEXT NOT NULL,
      title TEXT NOT NULL,
      content TEXT,
      create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `;
  createTable(db, 'timeline', timelineTableSQL);
}

// 封装数据库操作方法
const dbOperations = {
  // 获取所有文章
  getArticles: (db, options = {}) => {
    const { page = 1, limit = 3, category = null, search = null } = options;
    const offset = (page - 1) * limit;
    
    let query = 'SELECT * FROM articles';
    const params = [];
    const conditions = [];
    
    if (category) {
      conditions.push('category = ?');
      params.push(category);
    }
    
    if (search) {
      conditions.push('(title LIKE ? OR content LIKE ?)');
      params.push(`%${search}%`, `%${search}%`);
    }
    
    if (conditions.length > 0) {
      query += ' WHERE ' + conditions.join(' AND ');
    }
    
    query += ' ORDER BY create_time DESC LIMIT ? OFFSET ?';
    params.push(limit, offset);
    
    return new Promise((resolve, reject) => {
      db.all(query, params, (err, rows) => {
        if (err) {
          console.error('获取文章失败:', err.message);
          reject(err);
        } else {
          // 获取总记录数用于分页
          let countQuery = 'SELECT COUNT(*) as total FROM articles';
          if (conditions.length > 0) {
            countQuery += ' WHERE ' + conditions.join(' AND ');
          }
          
          db.get(countQuery, params.slice(0, params.length - 2), (countErr, countRow) => {
            if (countErr) {
              console.error('获取文章总数失败:', countErr.message);
              reject(countErr);
            } else {
              resolve({
                articles: rows,
                total: countRow.total,
                page,
                limit,
                totalPages: Math.ceil(countRow.total / limit)
              });
            }
          });
        }
      });
    });
  },
  
  // 获取单篇文章详情
  getArticleById: (db, articleId) => {
    return new Promise((resolve, reject) => {
      db.get('SELECT * FROM articles WHERE article_id = ?', [articleId], (err, row) => {
        if (err) {
          console.error('获取文章详情失败:', err.message);
          reject(err);
        } else {
          // 增加浏览量
          if (row) {
            db.run('UPDATE articles SET views = views + 1 WHERE article_id = ?', [articleId], (updateErr) => {
              if (updateErr) {
                console.error('更新浏览量失败:', updateErr.message);
              }
              resolve(row);
            });
          } else {
            resolve(null);
          }
        }
      });
    });
  },
  
  // 获取分类统计
  getCategoryStats: (db) => {
    return new Promise((resolve, reject) => {
      db.all(
        'SELECT category, COUNT(*) as count FROM articles GROUP BY category',
        (err, rows) => {
          if (err) {
            console.error('获取分类统计失败:', err.message);
            reject(err);
          } else {
            resolve(rows);
          }
        }
      );
    });
  },
  
  // 获取热门文章
  getPopularArticles: (db, limit = 3) => {
    return new Promise((resolve, reject) => {
      db.all(
        'SELECT * FROM articles ORDER BY views DESC LIMIT ?',
        [limit],
        (err, rows) => {
          if (err) {
            console.error('获取热门文章失败:', err.message);
            reject(err);
          } else {
            resolve(rows);
          }
        }
      );
    });
  },
  
  // 新增文章
  addArticle: (db, articleData) => {
    return new Promise((resolve, reject) => {
      // 生成唯一文章ID（时间戳）
      const articleId = Date.now().toString();
      // 获取当前时间作为创建时间
      const createTime = new Date().toISOString();
      
      const {
        category,
        author,
        author_avatar_url,
        cover_url,
        title,
        content,
        read_time
      } = articleData;

      const sql = `
        INSERT INTO articles (
          article_id, category, create_time, author, author_avatar_url,
          cover_url, title, content, read_time
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;

      const params = [
        articleId, category, createTime, author, author_avatar_url || '',
        cover_url || '', title, content, read_time || null
      ];

      db.run(sql, params, function(err) {
        if (err) {
          console.error('添加文章失败:', err.message);
          reject(err);
        } else {
          resolve({ 
            success: true, 
            articleId,
            insertId: this.lastID 
          });
        }
      });
    });
  },
  
  // 更新文章
  updateArticle: (db, { articleId, articleData }) => {
    return new Promise((resolve, reject) => {
      const {
        category,
        author,
        author_avatar_url,
        cover_url,
        title,
        content,
        read_time
      } = articleData;

      const sql = `
        UPDATE articles SET
          category = ?,
          author = ?,
          author_avatar_url = ?,
          cover_url = ?,
          title = ?,
          content = ?,
          read_time = ?
        WHERE article_id = ?
      `;

      const params = [
        category, author, author_avatar_url || '',
        cover_url || '', title, content, read_time || null,
        articleId
      ];

      db.run(sql, params, function(err) {
        if (err) {
          console.error('更新文章失败:', err.message);
          reject(err);
        } else {
          resolve({ 
            success: true, 
            changedRows: this.changes 
          });
        }
      });
    });
  },
  
  // 删除文章
  deleteArticle: (db, articleId) => {
    return new Promise((resolve, reject) => {
      const sql = 'DELETE FROM articles WHERE article_id = ?';
      
      db.run(sql, [articleId], function(err) {
        if (err) {
          console.error('删除文章失败:', err.message);
          reject(err);
        } else {
          resolve({ 
            success: true, 
            affectedRows: this.changes 
          });
        }
      });
    });
  },


  // 日志相关操作
  getTimelineEntries: (db) => {
    return new Promise((resolve, reject) => {
      db.all('SELECT * FROM timeline ORDER BY date DESC', (err, rows) => {
        if (err) {
          console.error('获取日志失败:', err.message);
          reject(err);
        } else {
          resolve(rows);
        }
      });
    });
  },
  
  getTimelineEntryById: (db, id) => {
    return new Promise((resolve, reject) => {
      db.get('SELECT * FROM timeline WHERE id = ?', [id], (err, row) => {
        if (err) {
          console.error('获取日志详情失败:', err.message);
          reject(err);
        } else {
          resolve(row);
        }
      });
    });
  },
  
  addTimelineEntry: (db, entryData) => {
    return new Promise((resolve, reject) => {
      const { date, type, title, content } = entryData;
      
      // 生成北京时间（UTC+8）的ISO字符串
      const beijingTime = new Date();
      beijingTime.setHours(beijingTime.getHours() + 8); // 转换为UTC+8
      const createTime = beijingTime.toISOString().slice(0, 19).replace('T', ' ');
      
      const sql = `
        INSERT INTO timeline (date, type, title, content, create_time)
        VALUES (?, ?, ?, ?, ?)
      `;
      
      
      db.run(sql, [date, type, title, content || '', createTime], function(err) {
        if (err) {
          console.error('添加日志失败:', err.message);
          reject(err);
        } else {
          resolve({ 
            success: true, 
            id: this.lastID 
          });
        }
      });
    });
  },
  
  updateTimelineEntry: (db, { id, entryData }) => {
    return new Promise((resolve, reject) => {
      const { date, type, title, content } = entryData;
      
      const sql = `
        UPDATE timeline SET
          date = ?,
          type = ?,
          title = ?,
          content = ?
        WHERE id = ?
      `;
      
      db.run(sql, [date, type, title, content || '', id], function(err) {
        if (err) {
          console.error('更新日志失败:', err.message);
          reject(err);
        } else {
          resolve({ 
            success: true, 
            changedRows: this.changes 
          });
        }
      });
    });
  },
  
  deleteTimelineEntry: (db, id) => {
    return new Promise((resolve, reject) => {
      const sql = 'DELETE FROM timeline WHERE id = ?';
      
      db.run(sql, [id], function(err) {
        if (err) {
          console.error('删除日志失败:', err.message);
          reject(err);
        } else {
          resolve({ 
            success: true, 
            affectedRows: this.changes 
          });
        }
      });
    });
  }
};

// 导出模块
module.exports = {
  initDatabase,
  createTables,
  dbOperations
};